﻿<?xml version="1.0" encoding="utf-8" ?>
<AdoNetDataAccess connection="$ArticleDBConnection$" formatter="bizobjlist of Article(primaryPicture)">
	<FormatterInfo>
		<code for="vars" on="Article">
		int firstDepth = -1;
		bool resetDepth = false;
		bool stripLeading = true;
		bool setup = false;
		</code>
		
		<code for="postEach" on="Article"><![CDATA[
		try
		{
			if(!setup)
			{        
			  if(info.get("stripLeading") is bool)
				stripLeading = (bool)info.get("stripLeading");
			  else if(info.get("stripLeading") is string)
				stripLeading = (string.Compare((string)info.get("stripLeading"), "False", true) == 0)?false:true;
			  resetDepth = info.get("resetDepth") is string && string.Compare((string)info.get("resetDepth"), "true", true) == 0;
			  setup = true;
			}
          
			if(CurrentBizObj.get("mediaCacheName").ToString().Length==0)
				CurrentBizObj.put("mediaCacheName",info.get("defaultCacheName"));
				
			if(stripLeading)
			{
			  string title = (string)CurrentBizObj.get("baseTitle");
			  int titIndex = title.IndexOf("~!");
			  if(titIndex != -1)
				CurrentBizObj.put("baseTitle", title.Substring(titIndex+2));
			}
		}
		catch(Exception e)
		{
		}
			 
		try
		{
			//The problem with the following 3 lines is that when we chunk the articles
			//we dont know what the real start dept is.  We need this to be a parameter into
			//the call so we know what the true start depth really is.
			if(resetDepth)
			{
				  if(firstDepth == -1)
					  firstDepth = (int)CurrentBizObj.get("depth");
				  CurrentBizObj.put("depth", (int)CurrentBizObj.get("depth") - firstDepth);						
	        }
		}
		catch(Exception e)
		{
		
		}			
		]]></code>
		
		<code for="bizObjType" on="Article"><![CDATA[
			obj = App.bizObjMgr.create(info.get("BizObjType") != null?info.get("BizObjType").ToString():"Article");
		]]></code>
		<code for="bizObjListType" on="Article"><![CDATA[
			m_list = App.bizObjMgr.createList(info.get("BizObjType") != null?info.get("BizObjType").ToString():"Article");
		]]></code>
	</FormatterInfo>
	<Sql>
		<code>
  <![CDATA[
		DetailLevel details = (DetailLevel)info.get("listDetailLevel");
		bool isPaging = false;
		if(info.get("isPaging") is bool)
			isPaging = (bool)info.get("isPaging");
		bool useTempTable = true;
			
			
		//---Order by support: -------------------------------------
		string orderBy = "nc.keyName";
		StringBuilder orderFields = new StringBuilder(orderBy);
		string orderByStr = (string)info.get("orderBy");
		if(Cmn.IsEmpty(orderByStr) && criteria != null && ((IBizObjList)criteria.get("sortOptions")).numElements != 0)
		{
			StringBuilder sortInfo = new StringBuilder(512);
			orderFields.Length = 0;
			foreach(IBizObj sortOp in criteria.get("sortOptions") as IBizObjList)
			{
				if(sortInfo.Length > 0)
				{
					sortInfo.Append(",");
					orderFields.Append(",");
				}
				sortInfo.Append(sortOp.get("field"));
				orderFields.Append(sortOp.get("field"));
				if(Cmn.IsFalse(sortOp.get("isAsc")))
					sortInfo.Append(" DESC");
			}
			orderByStr = sortInfo.ToString();
		}
		else if(orderByStr != null && string.Compare("keyname", orderByStr, true) != 0)
		{
			orderFields.Length = 0;
			if(string.Compare("lastChildUpdated", orderByStr, true) == 0)
			{
				orderBy = "nc.lastChildUpdated ";
				orderFields.Append("nc.lastchildUpdated");
			}
			else
			{
				orderBy = orderByStr;
				orderFields.Append(orderByStr);
			}
		}
		if(!Cmn.IsEmpty(orderByStr))
			orderBy = orderByStr;

		if(info.get("sortDir")!= null)
			orderBy += info.get("sortDir").ToString();
		
		//------ Columns configuration ---------------------------------------------
		StringBuilder columns = new StringBuilder(1024);
		columns.Append("a.id,a.isSystem,");

		if(details > DetailLevel.idOnly)
			columns.Append("a.baseTitle,a.uniqueTitle,a.readMoreJump,");
		if(details > DetailLevel.idAndTitle)
		{
			columns.Append(@"
				a.summary AS baseSummary, a.keyword, a.publishAuthorName, a.publishDate, nc.dateUpdated AS counts_dateUpdated, nc.dateCreated AS counts_dateCreated, a.metaType AS suggestedDisplayType,
				u.lastName AS counts_author_lastName, u.id AS counts_author_id, u.firstName AS counts_author_firstName, u.userName AS counts_author_userName, 
				u.dateCreated AS counts_author_dateCreated, a.id AS primaryPictureId,
				a.primaryMediaName AS primaryPictureName, a.primaryMediaName AS primaryPictureFileName, a.caption AS primaryPictureCaption, 
			");
			if(Cmn.IsTrue(info.get("includeTranslations"))) 
				columns.Append("a.translatedTitles,a.translatedSummaries,a.translationDetails,a.translatedCaptions,");
		}
		if(details > DetailLevel.basicDetails)
		{
			columns.Append("a.body AS baseBody,");
			if(true.Equals(info.get("includeTranslations")))
				columns.Append("a.translatedBodies,");
		}

		//---ECOM: --------------------------
		if(Cmn.IsTrue(info.get("includeECom")))
		{
			columns.Append(@"ec.partId,");
			if(details > DetailLevel.idAndTitle)
				columns.Append(@"ec.avgCostPrice,ec.listPrice,ec.sellPrice,ec.sellPriceDescription,");
			if(details > DetailLevel.basicDetails)
				columns.Append(@"ec.supplierPartId,ec.supplierId,
					ec.weight AS weightamount,weightUnit,
					ec.width AS [dimensions_width_amount],ec.widthUnit AS [dimensions_width_unit],
					ec.length AS [dimensions_length_amount],ec.lengthUnit AS [dimensions_length_unit],
					ec.depth AS [dimensions_depth_amount],ec.depthUnit AS [dimensions_depth_unit],
					ec.pricingId,");
		}

		//---ECOM: --------------------------
		if(Cmn.IsTrue(info.get("includeAuthorContactDetail")))
		{
			columns.Append(@"con.avitarImage AS counts_author_contact_avitarImage, con.location AS counts_author_contact_location,");
    }

		//---Calendar Columns: --------------------------
		if(Cmn.IsTrue(info.get("includeCal")))
			columns.Append(@"cal.calendarType,");
		else if(Cmn.IsTrue(info.get("includeCalEvt")))
		{
			columns.Append(@"cal.extraContentId AS extraContent_id, cal.startTime,cal.endTime,cal.eventTypeId AS type_id, cal.allDayEvent, cal.maxCapacity, ");
			columns.Append(@"calEv.backColor AS type_backColor, calEv.foreColor AS type_forColor, calEv.allowEnrollment AS type_allowEnrollment, calEv.useColorForDateArea AS type_useColorForDateArea, ");
			columns.Append(@"calEv.mapImageName AS type_mapImageName, calEv.cacheName AS type_cacheName, ");
			columns.Append(@"ea.baseTitle as type_baseTitle, ");
			if(Cmn.IsTrue(info.get("includeLocations")))
			{
				columns.Append(@"ad.id AS location_id,ad.street1 as location_street1, ");
				columns.Append(@"ad.city as location_city,ad.stateProvince as location_stateProvince,ad.zipCode as location_zipCode, ");
				columns.Append(@"countries.code as location_country_regionCode,countries.englishName as location_country_englishName, ");
				columns.Append(@"ad.latitude as location_latitude,ad.longitude as location_longitude ,at.id as location_type_id, ");
				columns.Append(@"at.mapImageName as location_type_mapImageName,at.currentMapImageName as location_type_currentMapImageName, ");
				columns.Append(@"at.cacheName as location_type_cacheName,aa.baseTitle as location_type_baseTitle, ");
			}
		}

		if(Cmn.IsTrue(info.get("includeCalEType")))
		{
			columns.Append(@"cal.backColor, cal.foreColor, cal.useColorForDateArea, cal.allowEnrollment, cal.calendarId, ");
			columns.Append(@"cal.mapImageName, cal.cacheName, ");
		}

		//---Address Type Columns: --------------------
		if(Cmn.IsTrue(info.get("includeAdrType")))
			columns.Append(@"adr.id, adr.mapImageName, adr.currentMapImageName, adr.cacheName, ");

		//--Article Counts Columns: -------------------
		if(Cmn.IsTrue(info.get("includeCounts")))
			columns.Append(@"nc.totalL1 AS counts_totalL1, nc.totalL2 AS counts_totalL2, nc.totalL3 AS counts_totalL3,
				nc.totalL4 AS counts_totalL4, nc.totalViews AS counts_totalViews, nc.lastViewed AS counts_lastViewed,
				nc.lastChildUpdated AS counts_lastChildUpdated, lu.userName AS counts_lastChildUpdatedBy_userName, ");

		//---Base Tree Node Columns: -------------------					
		columns.Append("t.objectId, t.parentId, t.parentHostId, nc.keyName, nc.depth, nc.rootId, nc.treeName ");
		
		//---Tables Support: ----------------------------------------------
		StringBuilder tables = new StringBuilder(1024);

		tables.Append(@"Articles AS a 
		JOIN	ObjectTreeNodes AS t ON a.id = t.objectId
		JOIN ObjectTreeCounts as nc on nc.objectId = a.id ");

		if(details > DetailLevel.idAndTitle)
			tables.Append(@"
				LEFT OUTER JOIN	Users AS u ON u.id = nc.authorId 
			");

    if(Cmn.IsTrue(info.get("includeCounts")))
			tables.Append(@" LEFT OUTER JOIN Users AS lu ON lu.id = nc.lastChildUpdatedByUserId ");
			
		if(true.Equals(info.get("includeECom")))
			tables.Append(@"JOIN eComProducts AS ec ON ec.id = a.id ");
    
    if(Cmn.IsTrue(info.get("includeAuthorContactDetail")))
      tables.Append(@" LEFT OUTER JOIN Contacts AS con ON con.id = u.id ");
    

		if(Cmn.IsTrue(info.get("includeCal")))
			tables.Append(@"JOIN Calendars AS cal on cal.id = a.id ");
		else if(Cmn.IsTrue(info.get("includeCalEvt")))
		{
			tables.Append(@"JOIN CalendarEvents AS cal on cal.id = a.id ");
			tables.Append(@"LEFT OUTER JOIN CalendarEventTypes AS calEv on calEv.id = cal.eventTypeId ");
			tables.Append(@"LEFT OUTER JOIN Articles AS ea on ea.id = cal.eventTypeId ");
			if(Cmn.IsTrue(info.get("includeLocations")))
			{
				tables.Append(@"LEFT OUTER JOIN Addresses AS ad on cal.locationId = ad.id ");
				tables.Append(@"LEFT OUTER JOIN Countries on countries.code = ad.countryCode ");
				tables.Append(@"LEFT OUTER JOIN AddressTypes AS at on at.id = ad.typeId ");
				tables.Append(@"LEFT OUTER JOIN Articles AS aa on ad.id = aa.id ");
			}

		}
		else if(Cmn.IsTrue(info.get("includeCalEType")))
			tables.Append(@"JOIN CalendarEventTypes AS cal on cal.id = a.id ");
			
		if(Cmn.IsTrue(info.get("includeAdrType")))
			tables.Append(@"JOIN AddressTypes as adr on adr.id = a.id ");



		//---Filters : -----------------------------------------------------
		StringBuilder filters = new StringBuilder(1024);
		bool addAnd = true;
		bool entireTree = Convert.ToBoolean(info.get("entireTree"));
		bool includeSelf = Convert.ToBoolean(info.get("includeSelf"));
		bool parentIdProvided = !Cmn.IsEmpty(info.get("parentId"));
		if(parentIdProvided)
		{
			if(entireTree && includeSelf)
				filters.Append("t.parentHostId = @parentId");	
			else if(entireTree && !includeSelf)
				filters.Append("(t.parentHostId = @parentId AND t.id <> @parentId)");
			else if(!entireTree && includeSelf)
				filters.Append("(t.parentId = @parentId OR t.id = @parentId)");
			else if(!entireTree && !includeSelf)
				filters.Append("t.parentId = @parentId AND t.id <> @parentId");
		}
		else if(!entireTree)
			filters.Append("t.id = nc.rootId");
		else
			addAnd = false;
		if(addAnd)
			filters.Append(" AND ");

		int startDepth = info.get("startDepth") is int?(int)info.get("startDepth"):-1;
		int endDepth = info.get("endDepth") is int?(int)info.get("endDepth"):-1;
		if(startDepth != -1 && startDepth == endDepth)
			filters.Append("nc.depth = ").Append(startDepth).Append(" AND ");
		else if(startDepth != -1 && endDepth != -1)
			filters.AppendFormat("nc.depth >= {0} AND nc.depth <= {1} AND ", startDepth, endDepth);

		bool hasCriteria = false;
		string firstPerc = "", secondPerc = "", cx="x";
		if(criteria != null && ((IBizObjList)criteria.get("filterOptions")).numElements != 0)
		{
			filters.Append("(");
			foreach(IBizObj filOp in criteria.get("filterOptions") as IBizObjList)
			{
				cx = filOp.get("criteria") as string;
				if (string.Compare(cx, "s", true) == 0)
					firstPerc = "%";
				else if (string.Compare(cx, "e", true) == 0)
					secondPerc = "%";
				else if (string.Compare(cx, "c", true) == 0)
					firstPerc = secondPerc = "%";
				if (firstPerc.Length > 0 || secondPerc.Length > 0)
					cx = "like";
				if(filOp.get("field") != null && (string)filOp.get("field") != "")
				{
					hasCriteria = true;
					filters.AppendFormat(" {0} {1} '{3}{2}{4}'", filOp.get("field"), cx, filOp.get("value"), firstPerc, secondPerc);
					filters.Append(criteria.get("filterIsAnd").Equals(true)?" AND":"  OR");
				}
			}
			if(hasCriteria)
			{
				filters.Length -= 4;
				filters.Append(") AND ");
			}
			else
				filters.Length -= 1;
		}
		if(info.get("objectTreeName") is string)
		{
			filters.Append(" nc.treeName = @treeName ");
			addParameter(cmd,"objectTreeName","treeName","VarChar", info);
		}
		if(!parentIdProvided || !entireTree)
			filters.Append(" AND t.objectId = t.parentHostId ");
			
		filters.Append(" AND a.companyId = @companyId ");

		]]></code>

		<code><![CDATA[
		//---Paging Support: ----------------------------------------
		if(isPaging && useTempTable)
		{
			sql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
			sql.Append(orderBy);
			sql.Append(") AS rownum,");
			sql.Append(columns);
			sql.Append("\r\nINTO #tmp_tbl_client\r\n");
			sql.Append("\r\nFROM\r\n").Append(tables);
			sql.Append("\r\nWHERE\r\n").Append(filters);
			sql.Append(" CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #tmp_tbl_client(rownum) SELECT rownum, ");
			
			string[] cols = columns.ToString().Split(',');
			columns = new StringBuilder(1024);
			foreach (string c in cols)
			{
				int p = c.IndexOf(" AS ");
				if(p != -1)
					columns.Append(c.Substring(p + 4));
				else if((p = c.IndexOf('.')) != -1)
					columns.Append(c.Substring(p+1));
				else 
					columns.Append(c);
				columns.Append(",");
			}
			string clmns = columns.ToString(0, columns.Length-1);
			sql.Append(clmns);

			sql.Append(" FROM #tmp_tbl_client WHERE\nrownum BETWEEN (@currentPage-1)*@pageSize+1 AND @currentPage*@pageSize ");
		}
		else if(isPaging && !useTempTable)
		{
			sql.Append("declare @sort varchar(430)\r\n");
			sql.Append("declare @poss int\r\n");
			sql.Append("set @poss = (@currentPage -1)*@pageSize +1\r\n");
			
			sql.Append("SET rowcount @poss\r\n");
			sql.Append("select @sort = ");
			sql.Append(orderFields);
			sql.Append("\r\nFROM\r\n").Append(tables);
			sql.Append("\r\nWHERE\r\n").Append(filters);
			sql.Append("\r\nORDER BY ").Append(orderBy).Append(";\r\n");
			sql.Append("set rowcount @pageSize\r\n");
			sql.Append("SELECT ").Append(columns);
			sql.Append("\r\nFROM\r\n").Append(tables);
			sql.Append("\r\nWHERE\r\n").Append(filters);
			sql.Append(" AND ").Append(orderFields).Append(" >= @sort");
			sql.Append("\r\nORDER BY ").Append(orderBy);
		}
		else
		{
			int maxElements = -1;
			if(!Cmn.IsEmpty(info.get("maxElements")))
				maxElements = int.Parse(info.get("maxElements").ToString());
			if(maxElements > 0)
				sql.Append("SELECT TOP(").Append(maxElements).Append(") ").Append(columns);
			else
				sql.Append("SELECT ").Append(columns);
			sql.Append("\r\nFROM\r\n").Append(tables);
			sql.Append("\r\nWHERE\r\n").Append(filters);
			sql.Append("\r\nORDER BY ").Append(orderBy);
		}
		]]></code>;
	</Sql>
	
	<Param name="parentId" type="UniqueIdentifier" />
	<Param name="companyId" type="UniqueIdentifier" />
	<Param name="pageSize" type="Integer" />
	<Param name="currentPage" type="Integer" />
</AdoNetDataAccess>


